Excel Survival Kit - 2nd Edition by Anne Walsh

Author:Anne Walsh
Language: eng
Format: epub
Tags: Vlookup;pivot tables;power query;power pivot;power BI;xlookup
Published: 2021-04-10T11:46:44+00:00

Figure 4-7

5. Edit the table_array part of the formula to reflect the new data range: $A$1:$E$13. You can do this either by changing the $E$12 to $E$13 or by reselecting the range to include $A$1:$E$13. To do this, click on the selection box next to Table_array in the Function Arguments dialog, as shown in Figure 4-8, and highlight the range $A$1:$E$13. Click on the selection box again to return to the main Vlookup() dialog box.

Figure 4-8

6. Click OK and copy down the formula to cell I12. You should now see Ireland in cell I11.

Note Even though the error showed up in cell I11, you begin the correction in cell I2 because you want to make sure the correction is applied to all the formulas, not just the one it shows up in.

Using the Table Feature to Solve the Missing Data Problem

However, it might be better to just get around this issue and resolve the problem permanently, particularly if the table_array part of the formula is regularly getting additions. To do this, you can convert a table array (where you will be pulling your matching data from) into a table. You’ve already seen tables in Chapter 2, but here’s a quick reminder:

1. Open the file 04_Vlookup_missing_data_table.

2. In the Sales Territories sheet, click on cell C8 and press Ctrl+T (or select Insert | Table).

3. In the Create Table dialog box that appears, make sure the My Table Has Headers box is checked and click OK.

4. Click anywhere in the table, click the Design tab, type Sales under Table Name, and press Enter to name the table Sales (see Figure 4-9).

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.